/*
 * @Author: xzy 1453077527@qq.com
 * @Date: 2025-08-24 00:34:58
 * @LastEditors: xzy 1453077527@qq.com
 * @LastEditTime: 2025-08-26 17:04:02
 * @Description:
 */
package com.xtedu.examproject.dao.impl;

import com.xtedu.examproject.dao.base.ExamQuestionDao;
import com.xtedu.examproject.entity.ExamQuestion;
import com.xtedu.examproject.util.DBUtil;
import com.xtedu.examproject.util.PageHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExamQuestionDaoImpl implements ExamQuestionDao {
    @Override
    public int insert(ExamQuestion examQuestion) throws Exception {
        String sql = "insert into examquestions (question_title,question_option,question_answer,type_id,course_id,state) values(?,?,?,?,?,?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, examQuestion.getTitle());
            ps.setString(2, examQuestion.getOption());
            ps.setString(3, examQuestion.getAnswer());
            ps.setInt(4, examQuestion.getTypeId());
            ps.setInt(5, examQuestion.getCourseId());
            ps.setBoolean(6, examQuestion.isState());
            return ps.executeUpdate();
        } catch (Exception e) {
            throw new Exception(e);
        }
    }


    @Override
    public int update(ExamQuestion examQuestion) throws Exception {
        String sql = "update examquestions set question_title=?,question_option=?,question_answer=?,type_id=?,course_id=? where id=?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, examQuestion.getTitle());
            ps.setString(2, examQuestion.getOption());
            ps.setString(3, examQuestion.getAnswer());
            ps.setInt(4, examQuestion.getTypeId());
            ps.setInt(5, examQuestion.getCourseId());
            ps.setInt(6, examQuestion.getId());
            return ps.executeUpdate();
        } catch (Exception e) {
            throw new Exception(e);
        }
    }

    @Override
    public int delete(ExamQuestion examQuestion) throws Exception {
        String sql = "delete from examquestions where id=?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, examQuestion.getId());
            return ps.executeUpdate();
        } catch (Exception e) {
            throw new Exception(e);
        }
    }

    @Override
    public int deleteById(int id) throws Exception {
        String sql = "delete from examquestions where id=?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, id);
            return ps.executeUpdate();
        } catch (Exception e) {
            throw new Exception(e);
        }
    }

    @Override
    public ExamQuestion findById(int id) throws Exception {
        String sql = "select * from examquestions where id=?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            ExamQuestion examQuestion = new ExamQuestion();
            while (rs.next()) {
                examQuestion.setId(rs.getInt(1));
                examQuestion.setTitle(rs.getString(2));
                examQuestion.setOption(rs.getString(3));
                examQuestion.setAnswer(rs.getString(4));
                examQuestion.setTypeId(rs.getInt(5));
                examQuestion.setCourseId(rs.getInt(6));
            }
            return examQuestion;
        } catch (Exception e) {
            throw new Exception(e);
        }
    }

    @Override
    public List<ExamQuestion> findAll() throws Exception {
        String sql = "select * from examquestions";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ResultSet rs = ps.executeQuery();
            List<ExamQuestion> examQuestions = new ArrayList<ExamQuestion>();
            while (rs.next()) {
                ExamQuestion examQuestion = new ExamQuestion();
                examQuestion.setId(rs.getInt(1));
                examQuestion.setTitle(rs.getString(2));
                examQuestion.setOption(rs.getString(3));
                examQuestion.setAnswer(rs.getString(4));
                examQuestion.setTypeId(rs.getInt(5));
                examQuestion.setCourseId(rs.getInt(6));
                examQuestions.add(examQuestion);
            }
            return examQuestions;
        } catch (Exception e) {
            throw new Exception(e);
        }
    }

    @Override
    public PageHelper<ExamQuestion> findAllByPage(int pageIndex, int pageSize) throws Exception {
        String sql = """
                select eq.*,qt.type_name,c.course_name from examquestions eq
                LEFT JOIN questiontype qt on eq.type_id=qt.id
                LEFT JOIN courses c on eq.course_id=c.id
                limit ?,?
                """;
        String sql1 = """
                select CEIL(COUNT(*)/?) from examquestions eq
                LEFT JOIN questiontype qt on eq.type_id=qt.id
                LEFT JOIN courses c on eq.course_id=c.id
                """;
        int totalPages = 0;
        try (
                Connection conn = DBUtil.getConnection();
                PreparedStatement pst = conn.prepareStatement(sql);
                Connection conn1 = DBUtil.getConnection();
                PreparedStatement pst1 = conn1.prepareStatement(sql1)
        ) {
            pst.setInt(1, (pageIndex - 1) * pageSize);
            pst.setInt(2, pageSize);
            ResultSet resultSet = pst.executeQuery();
            List<ExamQuestion> examQuestions = new ArrayList<>();
            while (resultSet.next()) {
                ExamQuestion examQuestion = new ExamQuestion();
                examQuestion.setId(resultSet.getInt("id"));
                examQuestion.setTitle(resultSet.getString("question_title"));
                examQuestion.setOption(resultSet.getString("question_option"));
                examQuestion.setAnswer(resultSet.getString("question_answer"));
                examQuestion.setTypeId(resultSet.getInt("type_id"));
                examQuestion.setCourseId(resultSet.getInt("course_id"));
                examQuestion.setTypeName(resultSet.getString("type_name"));
                examQuestion.setCourseName(resultSet.getString("course_name"));
                examQuestions.add(examQuestion);
            }

            pst1.setInt(1, pageSize);
            ResultSet resultSet1 = pst1.executeQuery();
            while (resultSet1.next()) {
                totalPages = resultSet1.getInt(1);
            }
            return new PageHelper<>(examQuestions, pageIndex, pageSize, totalPages, "", pageIndex);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public PageHelper<ExamQuestion> findPageByRoleIds(int pageIndex, int pageSize, int... roleIds) throws Exception {
        return null;
    }

    /**
     * 按题型查询
     *
     * @param pageIndex 页码
     * @param pageSize  每页数据量
     * @return 页面信息
     */
    @Override
    public PageHelper<ExamQuestion> findPageByType(int questionTypeId, int pageIndex, int pageSize) {
        String sql = "SELECT e.*, q.type_name, c.course_name FROM examquestions e " +
                "LEFT JOIN questiontype q ON e.type_id=q.id " +
                "LEFT JOIN courses c ON e.course_id=c.id " +
                "WHERE e.type_id=? " +
                "LIMIT ?,?";

        String sql1 = "SELECT CEIL(COUNT(*)/?) FROM examquestions e " +
                "LEFT JOIN questiontype q ON e.type_id=q.id " +
                "LEFT JOIN courses c ON e.course_id=c.id " +
                "WHERE e.type_id=?";

        int totalPages = 0;
        try (
                Connection conn = DBUtil.getConnection();
                PreparedStatement pst = conn.prepareStatement(sql);
                PreparedStatement pst1 = conn.prepareStatement(sql1)
        ) {
            pst.setInt(1, questionTypeId);
            pst.setInt(2, (pageIndex - 1) * pageSize);
            pst.setInt(3, pageSize);

            pst1.setInt(1, pageSize);
            pst1.setInt(2, questionTypeId);

            ResultSet resultSet = pst.executeQuery();
            List<ExamQuestion> examQuestions = new ArrayList<>();
            while (resultSet.next()) {
                ExamQuestion examQuestion = new ExamQuestion();
                examQuestion.setId(resultSet.getInt("id"));
                examQuestion.setTitle(resultSet.getString("question_title"));
                examQuestion.setOption(resultSet.getString("question_option"));
                examQuestion.setAnswer(resultSet.getString("question_answer"));
                examQuestion.setTypeId(resultSet.getInt("type_id"));
                examQuestion.setCourseId(resultSet.getInt("course_id"));
                examQuestion.setTypeName(resultSet.getString("type_name"));
                examQuestion.setCourseName(resultSet.getString("course_name"));
                examQuestions.add(examQuestion);
            }

            ResultSet resultSet1 = pst1.executeQuery();
            if (resultSet1.next()) {
                totalPages = resultSet1.getInt(1);
            }
            return new PageHelper<>(examQuestions, pageIndex, pageSize, totalPages, "", pageIndex);
        } catch (Exception e) {
            e.printStackTrace(); // 打印详细错误信息
            throw new RuntimeException("SQL执行错误: " + e.getMessage(), e);
        }
    }


    /**
     * 按课程查询
     *
     * @param pageIndex 页码
     * @param pageSize  每页数据量
     * @return 页面信息
     */
    @Override
    public PageHelper<ExamQuestion> findPageByCourse(int courseId, int pageIndex, int pageSize) {
        String sql = """
                    SELECT e.*,q.type_name,c.course_name FROM examquestions e
                    LEFT JOIN questiontype q ON e.type_id=q.id
                    LEFT JOIN courses c ON e.course_id=c.id
                    WHERE e.course_id=?
                    limit ?,?
                """;
        String sql1 = """
                SELECT CEIL(COUNT(*)/?) FROM examquestions e
                    LEFT JOIN questiontype q ON e.type_id=q.id
                    LEFT JOIN courses c ON e.course_id=c.id
                    WHERE e.course_id=?
                """;
        int totalPages = 0;
        try (
                Connection conn = DBUtil.getConnection();
                PreparedStatement pst = conn.prepareStatement(sql);
                Connection conn1 = DBUtil.getConnection();
                PreparedStatement pst1 = conn1.prepareStatement(sql1)
        ) {
            pst.setInt(1, courseId);
            pst.setInt(2, (pageIndex - 1) * pageSize);
            pst.setInt(3, pageSize);
            ResultSet resultSet = pst.executeQuery();
            List<ExamQuestion> examQuestions = new ArrayList<>();
            while (resultSet.next()) {
                ExamQuestion examQuestion = new ExamQuestion();
                examQuestion.setId(resultSet.getInt("id"));
                examQuestion.setTitle(resultSet.getString("question_title"));
                examQuestion.setOption(resultSet.getString("question_option"));
                examQuestion.setAnswer(resultSet.getString("question_answer"));
                examQuestion.setTypeId(resultSet.getInt("type_id"));
                examQuestion.setCourseId(resultSet.getInt("course_id"));
                examQuestion.setTypeName(resultSet.getString("type_name"));
                examQuestion.setCourseName(resultSet.getString("course_name"));
                examQuestions.add(examQuestion);
            }

            pst1.setInt(1, pageSize);
            ResultSet resultSet1 = pst1.executeQuery();
            while (resultSet1.next()) {
                totalPages = resultSet1.getInt(1);
            }
            return new PageHelper<>(examQuestions, pageIndex, pageSize, totalPages, "", pageIndex);
        } catch (Exception e) {
            throw new RuntimeException("sql执行错误");
        }
    }

    /**
     * 按专业查询
     *
     * @param pageIndex 页码
     * @param pageSize  每页数据量
     * @return 页面信息
     */
    @Override
    public PageHelper<ExamQuestion> findPageBySubject(int subjectId, int pageIndex, int pageSize) {
        return null;
    }

    @Override
    public int[] insertBatch(List<Map<String, Object>> data) {
        String sql = "INSERT INTO examquestions (question_title, question_option, question_answer, type_id, course_id, state) VALUES (?, ?, ?, ?, ?, ?)";
        int[] results = null;
        try (
                Connection conn = DBUtil.getConnection();
                PreparedStatement pstmt = conn.prepareStatement(sql)
        ) {
            for (Map<String, Object> item : data) {
                pstmt.setString(1, (String) item.get("question_title"));
                pstmt.setString(2, (String) item.get("question_option"));
                pstmt.setString(3, (String) item.get("question_answer"));
                pstmt.setObject(4, item.get("type_id"));
                pstmt.setObject(5, item.get("course_id"));
                pstmt.setObject(6, item.get("state"));
                // pstmt.addBatch() 是 JDBC 中用于批量处理的方法
                // 将当前的 SQL 语句参数组合添加到批处理队列中，而不是立即执行，避免频繁的数据库交互，提高大批量数据插入的效率
                pstmt.addBatch();
            }
            results = pstmt.executeBatch();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return results;
    }

    @Override
    public List<ExamQuestion> randomQuestions(int courseId, int questionTypeId, int questionCount) {
        String sql = """
                    SELECT e.*,q.type_name,c.course_name
                    FROM examquestions e
                    LEFT JOIN questiontype q ON e.type_id=q.id
                    LEFT JOIN courses c ON e.course_id=c.id
                    WHERE course_id=? AND type_id=?
                    ORDER BY RAND() LIMIT ?
                """;
        try (
                Connection conn = DBUtil.getConnection();
                PreparedStatement pstmt = conn.prepareStatement(sql)
        ) {
            pstmt.setInt(1, courseId);
            pstmt.setInt(2, questionTypeId);
            pstmt.setInt(3, questionCount);
            ResultSet resultSet = pstmt.executeQuery();
            List<ExamQuestion> examQuestions = new ArrayList<>();
            while (resultSet.next()) {
                ExamQuestion examQuestion = new ExamQuestion();
                examQuestion.setId(resultSet.getInt("id"));
                examQuestion.setTitle(resultSet.getString("question_title"));
                examQuestion.setOption(resultSet.getString("question_option"));
                examQuestion.setAnswer(resultSet.getString("question_answer"));
                examQuestion.setTypeId(resultSet.getInt("type_id"));
                examQuestion.setCourseId(resultSet.getInt("course_id"));
                examQuestion.setTypeName(resultSet.getString("type_name"));
                examQuestion.setCourseName(resultSet.getString("course_name"));
                examQuestions.add(examQuestion);
            }
            return examQuestions;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 获取问题类型统计信息
     * @return 问题类型统计信息
     */
    public List<Map<String, Object>> getQuestionTypeStats() throws Exception {
        String sql = "SELECT eq.type_id, qt.type_name, COUNT(*) AS count " +
                "FROM examquestions eq " +
                "LEFT JOIN questiontype qt ON eq.type_id = qt.id " +
                "GROUP BY eq.type_id, qt.type_name";

        List<Map<String, Object>> stats = new ArrayList<>();
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {

            while (rs.next()) {
                Map<String, Object> stat = new HashMap<>();
                stat.put("typeId", rs.getInt("type_id"));
                stat.put("typeName", rs.getString("type_name"));
                stat.put("count", rs.getInt("count"));
                stats.add(stat);
            }
        }
        return stats;
    }


    /**
     * 根据课程和题型查询题目
     * @param courseId 课程ID，可为null表示不限制课程
     * @param questionTypeId 题型ID，可为null表示不限制题型
     * @param pageIndex 页码
     * @param pageSize 每页数据量
     * @return 分页结果
     */
    @Override
    public PageHelper<ExamQuestion> findPageByCourseAndType(Integer courseId, Integer questionTypeId, int pageIndex, int pageSize) {
        // 构建查询条件
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("SELECT e.*, q.type_name, c.course_name FROM examquestions e ");
        sqlBuilder.append("LEFT JOIN questiontype q ON e.type_id=q.id ");
        sqlBuilder.append("LEFT JOIN courses c ON e.course_id=c.id ");

        StringBuilder whereClause = new StringBuilder();
        List<Object> params = new ArrayList<>();

        if (courseId != null) {
            whereClause.append("e.course_id=? AND ");
            params.add(courseId);
        }

        if (questionTypeId != null) {
            whereClause.append("e.type_id=? AND ");
            params.add(questionTypeId);
        }

        // 移除最后的 "AND "
        if (whereClause.length() > 0) {
            sqlBuilder.append("WHERE ").append(whereClause.substring(0, whereClause.length() - 5));
        }

        sqlBuilder.append(" LIMIT ?,?");

        String sql = sqlBuilder.toString();

        // 构建统计总数的SQL
        StringBuilder countSqlBuilder = new StringBuilder();
        countSqlBuilder.append("SELECT CEIL(COUNT(*)/?) FROM examquestions e ");
        countSqlBuilder.append("LEFT JOIN questiontype q ON e.type_id=q.id ");
        countSqlBuilder.append("LEFT JOIN courses c ON e.course_id=c.id ");

        if (whereClause.length() > 0) {
            countSqlBuilder.append("WHERE ").append(whereClause.substring(0, whereClause.length() - 5));
        }

        String countSql = countSqlBuilder.toString();

        int totalPages = 0;
        try (
                Connection conn = DBUtil.getConnection();
                PreparedStatement pst = conn.prepareStatement(sql);
                PreparedStatement pst1 = conn.prepareStatement(countSql)
        ) {
            // 设置查询参数
            int paramIndex = 1;
            for (Object param : params) {
                pst.setObject(paramIndex++, param);
            }
            pst.setInt(paramIndex++, (pageIndex - 1) * pageSize);
            pst.setInt(paramIndex, pageSize);

            // 设置统计查询参数
            pst1.setInt(1, pageSize);
            for (int i = 0; i < params.size(); i++) {
                pst1.setObject(2 + i, params.get(i));
            }

            // 执行查询
            ResultSet resultSet = pst.executeQuery();
            List<ExamQuestion> examQuestions = new ArrayList<>();
            while (resultSet.next()) {
                ExamQuestion examQuestion = new ExamQuestion();
                examQuestion.setId(resultSet.getInt("id"));
                examQuestion.setTitle(resultSet.getString("question_title"));
                examQuestion.setOption(resultSet.getString("question_option"));
                examQuestion.setAnswer(resultSet.getString("question_answer"));
                examQuestion.setTypeId(resultSet.getInt("type_id"));
                examQuestion.setCourseId(resultSet.getInt("course_id"));
                examQuestion.setTypeName(resultSet.getString("type_name"));
                examQuestion.setCourseName(resultSet.getString("course_name"));
                examQuestions.add(examQuestion);
            }

            // 获取总页数
            ResultSet resultSet1 = pst1.executeQuery();
            if (resultSet1.next()) {
                totalPages = resultSet1.getInt(1);
            }

            return new PageHelper<>(examQuestions, pageIndex, pageSize, totalPages, "", pageIndex);
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("SQL执行错误: " + e.getMessage(), e);
        }
    }

    /**
     * 搜索题目
     */
    @Override
    public PageHelper<ExamQuestion> searchQuestions(Integer courseId, Integer questionTypeId, String searchText, int pageIndex, int pageSize) {
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("SELECT e.*, q.type_name, c.course_name FROM examquestions e ");
        sqlBuilder.append("LEFT JOIN questiontype q ON e.type_id=q.id ");
        sqlBuilder.append("LEFT JOIN courses c ON e.course_id=c.id ");

        StringBuilder whereClause = new StringBuilder();
        List<Object> params = new ArrayList<>();

        if (courseId != null) {
            whereClause.append("e.course_id=? AND ");
            params.add(courseId);
        }

        if (questionTypeId != null) {
            whereClause.append("e.type_id=? AND ");
            params.add(questionTypeId);
        }

        if (searchText != null && !searchText.isEmpty()) {
            whereClause.append("(e.question_title LIKE ? OR e.question_option LIKE ? OR e.question_answer LIKE ?) AND ");
            params.add("%" + searchText + "%");
            params.add("%" + searchText + "%");
            params.add("%" + searchText + "%");
        }

        // 移除最后的 "AND "
        if (whereClause.length() > 0) {
            sqlBuilder.append("WHERE ").append(whereClause.substring(0, whereClause.length() - 5));
        }

        sqlBuilder.append(" ORDER BY e.id DESC LIMIT ?,?");

        String sql = sqlBuilder.toString();

        // 构建统计总数的SQL
        StringBuilder countSqlBuilder = new StringBuilder();
        countSqlBuilder.append("SELECT CEIL(COUNT(*)/?) FROM examquestions e ");
        countSqlBuilder.append("LEFT JOIN questiontype q ON e.type_id=q.id ");
        countSqlBuilder.append("LEFT JOIN courses c ON e.course_id=c.id ");

        if (whereClause.length() > 0) {
            countSqlBuilder.append("WHERE ").append(whereClause.substring(0, whereClause.length() - 5));
        }

        String countSql = countSqlBuilder.toString();

        int totalPages = 0;
        try (
                Connection conn = DBUtil.getConnection();
                PreparedStatement pst = conn.prepareStatement(sql);
                PreparedStatement pst1 = conn.prepareStatement(countSql)
        ) {
            // 设置查询参数
            int paramIndex = 1;
            for (Object param : params) {
                pst.setObject(paramIndex++, param);
            }
            pst.setInt(paramIndex++, (pageIndex - 1) * pageSize);
            pst.setInt(paramIndex, pageSize);

            // 设置统计查询参数
            pst1.setInt(1, pageSize);
            for (int i = 0; i < params.size(); i++) {
                pst1.setObject(2 + i, params.get(i));
            }

            // 执行查询
            ResultSet resultSet = pst.executeQuery();
            List<ExamQuestion> examQuestions = new ArrayList<>();
            while (resultSet.next()) {
                ExamQuestion examQuestion = new ExamQuestion();
                examQuestion.setId(resultSet.getInt("id"));
                examQuestion.setTitle(resultSet.getString("question_title"));
                examQuestion.setOption(resultSet.getString("question_option"));
                examQuestion.setAnswer(resultSet.getString("question_answer"));
                examQuestion.setTypeId(resultSet.getInt("type_id"));
                examQuestion.setCourseId(resultSet.getInt("course_id"));
                examQuestion.setTypeName(resultSet.getString("type_name"));
                examQuestion.setCourseName(resultSet.getString("course_name"));
                examQuestions.add(examQuestion);
            }

            // 获取总页数
            ResultSet resultSet1 = pst1.executeQuery();
            if (resultSet1.next()) {
                totalPages = resultSet1.getInt(1);
            }

            return new PageHelper<>(examQuestions, pageIndex, pageSize, totalPages, "", pageIndex);
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("SQL执行错误: " + e.getMessage(), e);
        }
    }


}
